package com.ls.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ls.domain.Confirm;
import com.ls.domain.Message;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ConfirmMapper extends BaseMapper<Confirm> {

    //学生查询本人消息 注解sql: sid、是否确认、当前页面、页面大小
    @Select("SELECT message.id, message.message_title, message.message_content, message.message_date\n" +
            "FROM message, student, confirm\n" +
            "WHERE message.id = confirm.mid AND student.id = confirm.sid\n" +
            "AND confirm.message_confirm = 0 AND student.id = #{sid}\n" +
            "LIMIT #{startIndex}, #{pageSize};")
    List<Message> selectBySid(Integer sid, int startIndex, int pageSize);

    //学生查询本人消息 注解sql: sid、是否确认、当前页面、页面大小的数量
    @Select("SELECT COUNT(*)\n" +
            "FROM message, student, confirm\n" +
            "WHERE message.id = confirm.mid AND student.id = confirm.sid\n" +
            "AND confirm.message_confirm = 0 AND student.id = #{sid};")
    int selectBySidCount(Integer sid);

    //学生搜索自己消息  注解sql：sid、是否确认、消息标题、当前页面、页面大小
    @Select("SELECT message.id, message.message_title, message.message_content, message.message_date\n" +
            "FROM message, student, confirm\n" +
            "WHERE message.id = confirm.mid AND student.id = confirm.sid\n" +
            "AND confirm.message_confirm = 0 AND student.id = #{sid}\n" +
            "AND message.message_title LIKE concat('%',#{messageTitle},'%')\n" +
            "LIMIT #{startIndex}, #{pageSize};")
    List<Message> selectBySidConditions(Integer sid, String messageTitle, int startIndex, int pageSize);

    //学生搜索自己消息  注解sql：sid、是否确认、消息标题、当前页面、页面大小的数量
    @Select("SELECT COUNT(*)\n" +
            "FROM message, student, confirm\n" +
            "WHERE message.id = confirm.mid AND student.id = confirm.sid\n" +
            "AND confirm.message_confirm = 0 AND student.id = #{sid}\n" +
            "AND message.message_title LIKE concat('%',#{messageTitle},'%');")
    int selectBySidConditionsCount(Integer sid, String messageTitle);

}
